#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--客户意向统计宽表
insert into hive.edu_dws.dws_customer_relationship_daycout
with tmp as (
select
--维度
    area,
    depart_id,
    depart_name,
    clue_state,
    origin_type,
    sub_id,
    sub_name,
    school_id,
    school_name,
    origin_channel,
    create_date_time,
--指标
    id,
    row_number() over(PARTITION BY id) as id_rn
from hive.edu_dwb.dwb_customer_relationship_detail order by id)
select
    area,
    depart_id,
    depart_name,
    clue_state,
    origin_type,
    sub_id,
    sub_name,
    school_id,
    school_name,
    origin_channel,
    substring(create_date_time,1,4) as year_code,
    substring(create_date_time,1,7) as year_month,
    substring(create_date_time,1,10) as year_month_day,

    case when grouping(area) = 0
         then 'area'
         when grouping(depart_id, depart_name) = 0
         then 'depart'
         when grouping(sub_id, sub_name) = 0
         then 'subject'
         when grouping(school_id, school_name) = 0
         then 'school'
         when grouping(origin_channel) = 0
         then 'origin_channel'
         when grouping(create_date_time) = 0
         then 'all'
         else 'other' end as group_type,

    case when grouping(area) = 0 --地区
         then count(if(id_rn = 1 and area is not null, id, null))
         when grouping(depart_id, depart_name) = 0 --咨询中心
         then count(if(id_rn = 1 and depart_id is not null, id, null))
         when grouping(sub_id, sub_name) = 0 --学科
         then count(if(id_rn = 1 and sub_id is not null, id, null))
         when grouping(school_id, school_name) = 0 --校区
         then count(if(id_rn = 1 and school_id is not null, id, null))
         when grouping(origin_channel) = 0 --来源渠道
         then count(if(id_rn = 1 and origin_channel is not null, id, null))
         when grouping(create_date_time) = 0 --日期+线上线下+新老会员
         then count(if(id_rn = 1 and create_date_time is not null, id, null))
         else null end as id_count
from tmp
group by
GROUPING SETS (
    --日期+线上线下+新老学员
    (create_date_time, origin_type, clue_state),
    --日期+线上线下+新老学员+地区
    (create_date_time, origin_type, clue_state, area),
    --日期+线上线下+新老学员+咨询中心维度
    (create_date_time, origin_type, clue_state, depart_id, depart_name),
    --日期+线上线下+新老学员+学科维度
    (create_date_time, origin_type, clue_state, sub_id, sub_name),
    --日期+线上线下+新老学员+校区维度
    (create_date_time, origin_type, clue_state, school_id, school_name),
    --日期+线上线下+新老学员+来源渠道
    (create_date_time, origin_type, clue_state, origin_channel)
);


insert into hive.edu_dws.dws_customer_clue_daycout
with tmp as (
select
--维度
    dt, --天
    origin_type,
    clue_state,
    substring(create_date_time,1,13) as datetime, --小时
    appeal_status,
--指标
    id,
    row_number() over(PARTITION BY id) as id_rn
from hive.edu_dwb.dwb_customer_clue_detail)
select
    case when grouping(clue_state) = 0
         then clue_state
         else null end as clue_state,
    case when grouping(origin_type) = 0
         then origin_type
         else null end as origin_type,
    case when grouping(appeal_status) = 0
         then appeal_status
         else null end as appeal_status,
    case when grouping(datetime) = 0
         then datetime
         else null end as datetime,

    case when grouping(datetime) = 0
         then 'time_hour'
         when grouping(dt) = 0
         then 'dt'
         else 'other' end as group_type,

    case when grouping(datetime) = 0 --天有效线索
         then count(if(id_rn = 1 and appeal_status is not null and appeal_status = 2, id, null))
         when grouping(dt) = 0 --小时有效线索
         then count(if(id_rn = 1 and appeal_status is not null and appeal_status = 2, id, null))
         else null end  as first_id_effective_count,

    case when grouping(datetime) = 0 --小时总线索
         then count(if(id_rn = 1 and appeal_status is not null,id , null))
         else null end as first_id_count,
    dt
from tmp
group by
grouping sets (
     (dt, origin_type, clue_state, appeal_status),
     (datetime, origin_type, clue_state, appeal_status)
);
"